/****** Object:  StoredProcedure [dbo].[dimMergedLocation]    Script Date: 24/01/2022 12:26:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 IF COL_LENGTH('[DW].[dLocation]', 'IsWarehouse') IS NULL
BEGIN
 ALTER TABLE DW.dLocation
 ADD IsWarehouse INT NULL 
 END

GO 

ALTER PROCEDURE [dbo].[dimMergedLocation]
@Location [dbo].[LocationType] READONLY

AS 
BEGIN

WITH tStoreLocation 
AS
(SELECT
		COALESCE(sSTO.[CompanyPrefix], sLOC.[CompanyPrefix]) AS [CompanyPrefix]
	   ,COALESCE(sSTO.[bigint_timestamp], sLOC.[bigint_timestamp]) AS [RowID]
	   ,COALESCE(sSLO.[Store No_], sSTO.[No_], sLOC.[Code]) AS [ReportingStore]
	   ,COALESCE(sSTO.[Name], NULL) AS [ReportingName]
	   ,COALESCE(sSTO.[No_], sLOC.[Code]) AS [LocationCode]
	   ,IIF(COALESCE(sSTO.[No_], sLOC.[Code]) = sSTO.[No_], 1, 0) AS [IsStore]
	   ,IIF(COALESCE(sSTO.[No_], sLOC.[Code]) = sLOC.[Code], 1, 0) AS [IsLocation]
	   ,COALESCE(sLOC.[Location is a Warehouse], 0) AS [IsWarehouse]
	   ,COALESCE(sSTO.[Name], sLOC.[Name]) [LocationName]
	   ,COALESCE(sSTO.[Store Manager ID], -1) AS [StoreManagerID]
	   ,COALESCE(sSTO.[Post Code], sLOC.[Post Code]) AS [PostCode]
	   ,COALESCE(sSTO.[City], sLOC.[City]) AS [City]
	   ,COALESCE(sSTO.[County], sLOC.[County]) AS [County]
	   ,COALESCE(sSTO.[Country Code], sLOC.[Country_Region Code]) AS [CountryCode]
	   ,COALESCE(sSTO.Longitude, NULL) AS [Longitude]
	   ,COALESCE(sSTO.Latitude, NULL) AS [Latitude]
	   ,COALESCE(sSTO.[Store size (square meters)], 0) AS [Store size (square meters)]
	FROM [Stg$Store] sSTO
	FULL JOIN [Stg$Location] sLOC
		ON sSTO.[No_] = sLOC.[Code]
		AND sSTO. [CompanyPrefix] = sLOC.[CompanyPrefix]
	FULL JOIN [Stg$Store Location] sSLO
		ON sLOC.[Code] = sSLO.[Location Code]
		AND sLOC.[CompanyPrefix] = sSLO.[CompanyPrefix]),

tStoreLocation2 
AS
(SELECT
		tSTL.[CompanyPrefix]
	   ,tSTL.RowID
	   ,tSTL.[ReportingStore]
	   ,COALESCE(MAX(tSTL.[ReportingName]) OVER (PARTITION BY tSTL.[ReportingStore]), tSTL.[LocationName]) AS [ReportingStoreName]
	   ,tSTL.[LocationCode]
	   ,tSTL.[IsStore]
	   ,tSTL.[IsLocation]
	   ,tSTL.[IsWarehouse]
	   ,tSTL.[LocationName]
	   ,tSTL.[StoreManagerID]
	   ,tSTL.[PostCode]
	   ,tSTL.[City]
	   ,tSTL.[County]
	   ,tSTL.[CountryCode]
	   ,COALESCE(AVG(tSTL.[Longitude]) OVER (PARTITION BY tSTL.[ReportingStore]), 0) AS [Longitude]
	   ,COALESCE(AVG(tSTL.[Latitude]) OVER (PARTITION BY tSTL.[ReportingStore]), 0) AS [Latitude]
	   ,tSTL.[Store size (square meters)]
	FROM [tStoreLocation] tSTL),

tMaxRowID 
AS
(SELECT
		tSL2.[CompanyPrefix]
	   ,MAX(tSL2.[RowID]) AS [RowID]
	   ,tSL2.[LocationCode]
	FROM [tStoreLocation2] tSL2
	GROUP BY tSL2.[CompanyPrefix]
			,tSL2.[LocationCode])

MERGE [DW].[dLocation] AS Target USING 
(SELECT DISTINCT
		COALESCE(dCOM.[SK_Company],-1) AS [Company]
	   ,tMRI.[RowID]
	   ,tSL2.[ReportingStore]
	   ,tSL2.[ReportingStoreName]
	   ,tSL2.[LocationCode]
	   ,tSL2.[IsStore]
	   ,tSL2.[IsLocation]
	   ,tSL2.[IsWarehouse]
	   ,tSL2.[LocationName]
	   ,tSL2.[StoreManagerID]
	   ,tSL2.[PostCode]
	   ,tSL2.[City]
	   ,tSL2.[County]
	   ,tSL2.[CountryCode]
	   ,tSL2.[Longitude]
	   ,tSL2.[Latitude]
	   ,tSL2.[Store size (square meters)]
	   ,GETUTCDATE() AS [Batchdate]
	FROM [tStoreLocation2] tSL2
	RIGHT JOIN [tMAXRowID] tMRI
		ON tSL2.[CompanyPrefix] = tMRI.[CompanyPrefix]
		AND tSL2.[LocationCode] = tMRI.[LocationCode]
		AND tSL2.[RowID] = tMRI.[RowID]
	LEFT JOIN [DW].[dCompany] dCOM
		ON tSL2.[CompanyPrefix] = dCOM.[CompanyPrefix]
) AS Source

ON TARGET.[LocationCode] = Source.[LocationCode]
	AND TARGET.[Company] = Source.[Company]

WHEN MATCHED
	THEN UPDATE
		SET [RowID] = Source.[RowID]
		   ,[ReportingStore] = Source.[ReportingStore]
		   ,[ReportingStoreName] = Source.[ReportingStoreName]
		   ,[LocationCode] = Source.[LocationCode]
		   ,[IsStore] = Source.[IsStore]
		   ,[IsLocation] = Source.[IsLocation]
		   ,[IsWarehouse] = Source.[IsWarehouse]
		   ,[LocationName] = Source.[LocationName]
		   ,[StoreManagerID] = Source.[StoreManagerID]
		   ,[PostCode] = Source.[PostCode]
		   ,[City] = Source.[City]
		   ,[County] = Source.[County]
		   ,[CountryCode] = Source.[CountryCode]
		   ,[Longitude] = Source.[Longitude]
		   ,[Latitude] = Source.[Latitude]
		   ,[Store size (square meters)] = Source.[Store size (square meters)]
		   ,[Batchdate] = Source.[Batchdate]

WHEN NOT MATCHED BY TARGET
	THEN INSERT ([Company], [RowID], [ReportingStore], [ReportingStoreName], [LocationCode], [IsStore], [IsLocation],[IsWarehouse], [LocationName], [StoreManagerID], [PostCode], [City], [County], [CountryCode], [Longitude], [Latitude], [Store size (square meters)], [Batchdate])
			VALUES (Source.[Company], Source.[RowID], Source.[ReportingStore], Source.[ReportingStoreName], Source.[LocationCode], Source.[IsStore], Source.[IsLocation],Source.[IsWarehouse], Source.[LocationName], Source.[StoreManagerID], Source.[PostCode], Source.[City], Source.[County], Source.[CountryCode], Source.[Longitude], Source.[Latitude], Source.[Store size (square meters)], Source.[Batchdate])

WHEN NOT MATCHED BY SOURCE
	AND TARGET.[SK_Location] <> -1
	THEN DELETE
;
;
END

SELECT
	'RowCount' = @@rowcount


--GO
--DECLARE	@return_value int

--EXEC	@return_value = [dbo].[dimMergedLocation]

--SELECT	'Return Value' = @return_value

--GO
